package com.yc.easy.web;

import com.yc.easy.res.DBRes;
import com.ly.common.util.IOUtils;
import com.ly.common.util.ZipUtils;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.yc.easy.util.SqlBuilder;
import com.yc.easy.bean.Result;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.*;
import java.lang.reflect.Field;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;

@RestController
@RequestMapping("easy")
public class EasyDo {

    protected Logger logger = LoggerFactory.getLogger(getClass());

    @Value("${ly.easy.restore-sh}")
    private String restoreSh;

    @Value("${ly.easy.backup-dir}")
    private String backupDir;

    private String defaultDbname = "demo";

    // 注入该对象的目的, 是为了确保 dbRes 在此之前构建完成, @DependOn 注解有问题
    @Resource
    DBRes dbRes;

    @GetMapping(value = {"/", ""})
    ModelAndView index(ModelAndView mav) {
        mav.setViewName("redirect:/easy/index.html");
        return mav;
    }

    @Transactional
    @RequestMapping("{table}/save")
    public Result<?> save(@PathVariable String table, @RequestParam Map<String, Object> colMapVals,
                          @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        if (colMapVals.get("id") != null) {
            check(table, colMapVals.get("id"), entryUser, colMapVals);
        } else {
            check(table, 0, entryUser, colMapVals);
        }
        String sql;
        List<Object> params = new ArrayList<>();
        try {
            if (colMapVals.containsKey("id")) {
                sql = SqlBuilder.buildUpdateById(table, colMapVals, params);
            } else {
                sql = SqlBuilder.buildInsert(table, colMapVals, params);
            }
            logger.debug("\nSQL参数：" + params + "\nSQL语句：" + sql);
            JdbcTemplate jdbc = getJdbc(colMapVals);
            jdbc.update(sql, params.toArray());
            Long id = jdbc.queryForObject("select LAST_INSERT_ID()", Long.class);
            colMapVals.put("id", id);
            return Result.success("保存成功!", colMapVals);
        } catch (Exception e) {
            e.printStackTrace();
            return Result.failure("保存失败!", e.getMessage());
        }
    }

    private void check(String table, Object id, Object entryUser, Map<String, Object> colMapVals) {
        boolean isNormalUser = isNormalUser(entryUser);
        if (table.contains(".") && isNormalUser) {
            throw new IllegalStateException("您无权执行该操作！");
        }
        if (id != null) {
            long myid = Long.parseLong("" + id);
            if (myid > 0) {
                if (isNormalUser && myid > 0 && myid <= maxid(table, colMapVals) ||
                        isNormalUser && myid <= 0) {
                    throw new IllegalStateException("演示数据不允许执行该操作！");
                }
            }
        } else {
            throw new IllegalStateException("请提交参数id的值！");
        }
    }

    private boolean isNormalUser(Object entryUser) {
        if (entryUser == null) {
            return true;
        } else {
            final Object role;
            if (entryUser instanceof Map) {
                Map<String, Object> map = (Map<String, Object>) entryUser;
                role = map.get("role");
            } else {
                try {
                    final Field rf = entryUser.getClass().getDeclaredField("role");
                    rf.setAccessible(true);
                    role = rf.get(entryUser);
                } catch (Exception e) {
                    e.printStackTrace();
                    return true;
                }
            }
            return Integer.parseInt(role + "") != 0;
        }
    }

    @RequestMapping("{table}/remove")
    public Result<?> remove(@PathVariable String table, long id, @RequestParam Map<String, Object> colMapVals,
                            @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        check(table, id, entryUser, colMapVals);
        List<Object> params = new ArrayList<>();
        String sql = SqlBuilder.buildDeleteById(table, id, params);
        try {
            logger.debug("\nSQL参数：" + params + "\nSQL语句：" + sql);
            getJdbc(colMapVals).update(sql, params.toArray());
            return Result.success("删除成功!");
        } catch (Exception e) {
            e.printStackTrace();
            return Result.failure("删除失败!", e.getMessage());
        }
    }

    @RequestMapping("{table}/get")
    public Result<?> get(@PathVariable String table, String id, @RequestParam Map<String, Object> colMapVals,
                         @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        check(table, 0, entryUser, colMapVals);
        if (id == null) {
            throw new IllegalStateException("请提交参数id的值！");
        }
        List<Object> params = new ArrayList<>();
        String sql = SqlBuilder.buildSelectById(table, id, params);
        try {
            logger.debug("\nSQL参数：" + params + "\nSQL语句：" + sql);
            Map<String, Object> one = getJdbc(colMapVals).queryForMap(sql, params.toArray());
            return Result.success("查询成功!", one);
        } catch (Exception e) {
            e.printStackTrace();
            return Result.failure("查询失败!", e.getMessage());
        }
    }

    /**
     * colMapVals.select => 自定义字段列表 缺省 *
     * colMapVals.join => 自定义join列表 join=uid=dm_user.id;pid=dm_product.id
     * 生成 => join dm_user b on a.uid=b.id
     * join dm_product c on a.pid = c.id
     */
    @RequestMapping("{table}/list")
    public List<Map<String, Object>> list(@PathVariable String table, @RequestParam Map<String, Object> colMapVals,
                                          @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        check(table, 0, entryUser, colMapVals);
        List<Object> params = new ArrayList<>();
        String sql = SqlBuilder.buildSelect(table, colMapVals, params);
        try {
            logger.debug("\nSQL参数：" + params + "\nSQL语句：" + sql);
            return getJdbc(colMapVals).queryForList(sql, params.toArray());
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @RequestMapping("{table}/backup")
    public Result<?> backup(@PathVariable String table, @RequestParam Map<String, Object> colMapVals,
                            @SessionAttribute(required = false) Object entryUser) throws IOException {
        logger.debug("请求参数: " + colMapVals);
        File backupDirFile = new File(backupDir);
        if (backupDirFile.exists() == false && backupDirFile.mkdirs() == false) {
            return Result.failure("备份失败, 原因: 创建备份目录失败!");
        }
        Map<String, Object> tableinfo = table(table, colMapVals, entryUser);
        JdbcTemplate jdbc = getJdbc(colMapVals);
        Long maxid = jdbc.queryForObject("select ifnull(max(id),0) maxid from " + table, Long.class);
        String comment = (String) tableinfo.get("comment");
        comment = comment.replaceAll("(.+?)(-\\d+)*", "$1");
        if (maxid > 0) {
            comment += "-" + maxid;
        }
        jdbc.execute(String.format("alter table %s comment '%s'", table, comment));
        String path = backupDir + table + ".sql";
        FileOutputStream out = new FileOutputStream(path);
        execCmd(buildBackupCmd(table), out, true);
        return Result.success("备份成功!", path);
    }


    @RequestMapping("{table}/restore")
    public Result<?> restore(@PathVariable String table, @RequestParam Map<String, Object> colMapVals,
                             @SessionAttribute(required = false) Object entryUser) throws IOException {
        logger.debug("请求参数: " + colMapVals);
        check(table, 0, entryUser, colMapVals);
        String path = backupDir + table + ".sql";
        if (new File(path).exists() == false) {
            return Result.failure("备份文件不存在", path);
        }
        OutputStream out = new ByteArrayOutputStream();
        execCmd(buildRestoreCmd(path), out, true);
        logger.info(out.toString());
        return Result.success("还原成功!", out.toString());
    }

    private void execCmd(String cmd, OutputStream out, boolean isClose) throws IOException {
        try {
            logger.info("execute: " + cmd);
            Process p = Runtime.getRuntime().exec(cmd);
            try (InputStream in = p.getInputStream();) {
                byte[] b = new byte[1024];
                int c;
                while ((c = in.read(b)) > 0) {
                    out.write(b, 0, c);
                }
            }
        } catch (Exception e) {
            if (out != null) {
                e.printStackTrace(new PrintWriter(out));
            }
        } finally {
            if (isClose && out != null) {
                out.close();
            }
        }
    }

    private String buildBackupCmd(String table) {
        return String.format("mysqldump -h%s -u%s -p%s %s %s",
                dbi.host, dbi.username, dbi.password, dbi.dbname, table);
    }

    private String buildRestoreCmd(String filepath) {
        return String.format("%s %s", restoreSh, filepath);
    }

    @RequestMapping("{table}/page")
    public Map<Object, Object> page(@PathVariable String table,
                                    @RequestParam Map<String, Object> colMapVals,
                                    @RequestParam(defaultValue = "1") int page,
                                    @RequestParam(defaultValue = "10") int size,
                                    @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        check(table, 0, entryUser, colMapVals);
        List<Object> params = new ArrayList<>();
        colMapVals.remove("page");
        colMapVals.remove("size");
        size = size > 100 ? 100 : size;
        String sql = SqlBuilder.buildSelect(table, colMapVals, params);
        try {
            String sql1 = String.format("select count(*) cnt from (%s) a", sql);
            String sql2 = String.format("select * from (%s) a limit %s,%s", sql, (page - 1) * size, size);
            logger.debug("\nSQL参数：" + params + "\nSQL语句：" + sql1 + "\nSQL语句：" + sql2);
            JdbcTemplate jdbc = getJdbc(colMapVals);
            Long cnt = jdbc.queryForObject(sql1, Long.class, params.toArray());
            List<Map<String, Object>> list = jdbc.queryForList(sql2, params.toArray());
            Map<Object, Object> ret = new HashMap<>();
            ret.put("total", cnt);
            ret.put("rows", list);
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    @GetMapping("tables")
    public List<Map<String, Object>> tables(@RequestParam Map<String, Object> colMapVals) {
        logger.debug("请求参数: " + colMapVals);
        String sql = "SELECT TABLE_NAME `name`, TABLE_COMMENT `comment`\n" +
                "FROM information_schema.TABLES\n" +
                "WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = '" + dbi.dbname + "' \n" +
                "ORDER BY TABLE_NAME";
        return getJdbc(colMapVals).queryForList(sql);
    }

    @GetMapping("table")
    public Map<String, Object> table(String name, @RequestParam Map<String, Object> colMapVals,
                                     @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        check(name, 0, entryUser, colMapVals);
        String sql = "SELECT TABLE_NAME `name`, TABLE_COMMENT `comment`\n" +
                "FROM information_schema.TABLES\n" +
                "WHERE TABLE_TYPE = 'BASE TABLE'\n" +
                "and TABLE_NAME = ? \n" +
                "and TABLE_SCHEMA = '" + dbi.dbname + "' \n" +
                "ORDER BY TABLE_NAME";
        try{
            return getJdbc(colMapVals).queryForMap(sql, name);
        } catch (EmptyResultDataAccessException e){
            return Collections.emptyMap();
        }
    }

    @GetMapping("columns")
    public List<Map<String, Object>> columns(String table, @RequestParam Map<String, Object> colMapVals,
                                             @SessionAttribute(required = false) Object entryUser) {
        logger.debug("请求参数: " + colMapVals);
        check(table, 0, entryUser, colMapVals);
        String sql = "SELECT COLUMN_NAME `name`, " +
                "COLUMN_DEFAULT `defaultValue`, " +
                "IS_NULLABLE `nullable`\n, " +
                "COLUMN_TYPE `type`, " +
                "COLUMN_COMMENT `comment`, " +
                "COLUMN_KEY `key`\n" +
                "FROM information_schema.COLUMNS\n" +
                "WHERE TABLE_NAME = ? and TABLE_SCHEMA = ?";
        return getJdbc(colMapVals).queryForList(sql, table, dbi.dbname);
    }

    private long maxid(String table, @RequestParam Map<String, Object> colMapVals) {
        Map<String, Object> tableinfo = table(table, colMapVals, null);
        String comment = (String) tableinfo.get("comment");
        if (comment != null && comment.matches(".+-\\d+")) {
            String maxid = comment.replaceAll(".+-(\\d+)", "$1");
            return Long.parseLong(maxid);
        } else {
            return 0;
        }
    }

    @ExceptionHandler(Exception.class)
    Result exHandler(Exception e) {
        if (e instanceof IllegalStateException) {
            return Result.failure(e.getMessage());
        } else {
            e.printStackTrace();
            return Result.failure("业务异常,请联系管理员！", e.getMessage());
        }
    }

    @RequestMapping("{table}/down")
    public Result down(@PathVariable String table, HttpServletResponse response, @RequestParam Map<String, Object> colMapVals,
                       @SessionAttribute(required = false) Object entryUser) throws IOException {
        logger.debug("请求参数: " + colMapVals);
        if (entryUser == null) {
            return Result.failure("请先登录系统");
        }
        check(table, 0, entryUser, colMapVals);
        String filename = table;
        String[] tables = table.split("\\s+");
        if (tables.length > 1) {
            Map<String, Object> t = table(tables[0], colMapVals, null);
            String comment = (String) t.get("comment");
            filename = comment.split("-")[0];
            File downDir = getDownDir(filename);
            if (downDir != null) {
                FileOutputStream out = new FileOutputStream(new File(downDir, filename + ".SQL"));
                execCmd(buildBackupCmd(table), out, false);
                return downloadHtml(filename, downDir, response);
            }
        }
        setDownFile(response, filename + ".SQL");
        ServletOutputStream out = response.getOutputStream();
        execCmd(buildBackupCmd(table), out, false);
        return null;
    }

    @Value("${ly.easy.html-dir}")
    private String htmlDir;

    private File getDownDir(String dir) {
        String localFilePath = htmlDir + dir;
        File localFileObj = new File(localFilePath);
        File staticFileObj = new File(localFileObj, dir);
        logger.debug("localFilePath = " + localFilePath);
        logger.debug("localFileObj = " + localFileObj);
        logger.debug("staticFileObj = " + staticFileObj);
        if (staticFileObj.exists()) {
            logger.debug("staticFileObj.exists() = " + staticFileObj.exists());
            return staticFileObj;
        } else if (localFileObj.exists()) {
            logger.debug("localFileObj.exists() = " + localFileObj.exists());
            return localFileObj;
        } else {
            return null;
        }
    }

    public Result<?> downloadHtml(String name, File downFile, HttpServletResponse response) throws IOException {
        logger.debug("打包下载: " + downFile.getAbsolutePath());
        if (downFile.isFile()) {
            setDownFile(response, name);
            IOUtils.exec((p) -> IOUtils.transfer((InputStream) p[0], response.getOutputStream())
                    , new FileInputStream(downFile));
        } else if (downFile.isDirectory()) {
            setDownFile(response, name + ".zip");
            ZipUtils.zip(response.getOutputStream(), downFile.getAbsolutePath());
        }
        return null;
    }

    public void setDownFile(HttpServletResponse response, String name) throws UnsupportedEncodingException {
        response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(name, "UTF-8"));
    }

    @PostConstruct
    public void init() {
        dbi = new DBInfo();
        DataSource dataSource = dbRes.getJdbc(defaultDbname).getDataSource();
        if (dataSource instanceof DriverManagerDataSource) {
            DriverManagerDataSource ds = (DriverManagerDataSource) dataSource;
            dbi.username = ds.getUsername();
            dbi.password = ds.getPassword();
            dbi.url = ds.getUrl();
        } else if (dataSource instanceof HikariDataSource) {
            HikariDataSource ds = (HikariDataSource) dataSource;
            dbi.username = ds.getUsername();
            dbi.password = ds.getPassword();
            dbi.url = ds.getJdbcUrl();
        }
        URL url;
        try {
            url = new URL(dbi.url.replace("jdbc:mysql", "http"));
        } catch (MalformedURLException e) {
            throw new RuntimeException("数据库URL错误:" + dbi.url, e);
        }
        dbi.host = url.getHost();
        dbi.dbname = url.getPath().substring(1);
        logger.debug("默认数据源: " + dbi);
    }

    private DBInfo dbi;

    private class DBInfo {
        private String host;
        private String dbname;
        private String url;
        private String username;
        private String password;

        @Override
        public String toString() {
            return "DBInfo{" +
                    "host='" + host + '\'' +
                    ", dbname='" + dbname + '\'' +
                    ", url='" + url + '\'' +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    '}';
        }
    }

    private JdbcTemplate getJdbc(Map<String, Object> colMapVals) {
        Object dbname = colMapVals.get("db");
        if (dbname == null) {
            dbname = colMapVals.get("dbname");
        }
        if (dbname == null) {
            dbname = defaultDbname;
        }
        JdbcTemplate jdbc = dbRes.getJdbc((String) dbname);
        logger.debug("使用数据源" + dbname + ": " + jdbc);
        return jdbc;
    }

}
